import Path from "path";
import ExcelJs from "exceljs";
import { runScript } from "./run-script.js";
import { Excel } from "../common/index.js";
import Fs from "fs-extra";
import { writeABook } from "./misc.js";

/**
 * 向xlsx写入的的数据对
 */
export interface XlsxData {
  /** 在sheet中写入数据的位置 */
  pos: string;
  /** 在该pos写的的数据的value */
  value: number | string | null | (number | string | undefined)[][];
}

/**
 * @description 调用python将数据写入到Excel
 * 需要 python 3.x 和 xlwings
 * @deprecated 已弃用
 * @param path Excel的文件路径
 * @param sheetName sheet的名称
 * @param data 要写入的数据
 */
export async function writeDataToExcel(
  /** Excel文件路径 */
  path: string,
  /** 要向哪个sheet写入数据 */
  sheetName: string,
  /** 要写入的数据 */
  data: XlsxData[]
): Promise<unknown> {
  path = Path.normalize(path).replace(/\\/g, "/");
  return await new Promise((resolve) =>
    runScript({
      filePath: Path.resolve(__dirname, "../assets/wite-data.py").replace(
        /\\/g,
        "/"
      ),
      atDir: Path.dirname(path),
      msgTo: { path, sheetName, data },
      onMsg({ success }: any): any {
        success && resolve(1);
      },
    })
  );
}

/**
 * 用于获取sheet的函数，如果无法获取，就创建并获取
 */
export type GetSheet = (sheetName: string) => ExcelJs.Worksheet;

/**
 * 向Excel文档写入内容的函数
 */
export type WriteData = (
  /**
   * 要写入的data
   */
  data: Excel.ExcelData,
  /**
   *  要在什么位置写入数据
   */
  pos?: Excel.RawInputPos,
  /**
   * 处理data和位置的函数
   */
  parser?: Excel.Parser,
  /**
   * 向哪个sheet名称，如果该sheet不存在，自动创建
   */
  sheet?: string
) => void;

/**
 * 用于操作Excel的工具
 */
export interface ExcelHandlerTools {
  /**
   * 用于获取sheet的函数，如果无法获取，就创建并获取
   */
  getSheet: GetSheet;
  /**
   * 向Excel文档写入内容的函数
   */
  writeData: WriteData;
  /**
   * workbook实例
   */
  wb: ExcelJs.Workbook;
}

/**
 * 操作Excel的函数，待该函数执行完成会关闭Excel
 */
export type ExcelTask = (
  /**
   * 用于操作Excel的工具
   */
  tools: ExcelHandlerTools
) => Promise<any | void> | any | void;

/**
 * 使用ExcelJs读写Excel数据
 * @param fileName 文件位置
 * @param task 在excel关闭之前需要做的任务
 * @param saveTo 保存文件位置，默认 和 filename 相同
 */
export async function handleExcel(
  fileName: string,
  task: ExcelTask = () => 0,
  saveTo: string = fileName
) {
  let _exists = Fs.existsSync(fileName);
  if (!_exists) {
    await writeABook(fileName, []);
  }
  let stream = Fs.createReadStream(fileName);
  let wb = new ExcelJs.Workbook();
  if (/.csv$/i.test(fileName)) {
    await wb.csv.read(stream);
  } else {
    await wb.xlsx.read(stream);
  }
  stream.close();
  let getSheet: GetSheet = (sheetName: string): ExcelJs.Worksheet => {
    let _sheetNames: string[] = [];
    wb.eachSheet((sheet) => {
      _sheetNames.push(sheet.name);
    });
    if (!_sheetNames.includes(sheetName)) {
      wb.addWorksheet(sheetName);
    }
    return wb.getWorksheet(sheetName);
  };
  let writeData: WriteData = (
    data: Excel.ExcelData,
    pos: Excel.RawInputPos = "A1",
    parser: Excel.Parser = (e) => e.value || "",
    sheet: string = wb.worksheets[0].name
  ): void => {
    let toWrite = Excel.distribute(pos, sheet, data, parser);
    let currentSheet = getSheet(sheet);
    for (let { value, cell } of toWrite) {
      currentSheet.getCell(cell).value = value;
    }
  };
  await task({ getSheet, writeData, wb });
  await wb.xlsx.writeFile(saveTo);
}
